iT邦幫忙

DAY 18
1

再接再厲,繼續研究csvkit這好東西.

找出我們想要的資料

一般的UNIX指令是grep;在SQL中是用where.
csvkit中提供了csvgrep.

來看範例:

$ csvcut -c county,item_name,total_cost data.csv | csvgrep -c county -m LANCASTER | csvlook
|------------+--------------------------------+-------------|
|  county    | item_name                      | total_cost  |
|------------+--------------------------------+-------------|
|  LANCASTER | RIFLE,5.56 MILLIMETER          | 120         |
|  LANCASTER | RIFLE,5.56 MILLIMETER          | 120         |
|  LANCASTER | RIFLE,5.56 MILLIMETER          | 120         |
|  LANCASTER | RIFLE,5.56 MILLIMETER          | 120         |
|  LANCASTER | RIFLE,5.56 MILLIMETER          | 120         |
|  LANCASTER | RIFLE,5.56 MILLIMETER          | 120         |
|  LANCASTER | RIFLE,5.56 MILLIMETER          | 120         |
|  LANCASTER | RIFLE,5.56 MILLIMETER          | 120         |
|  LANCASTER | RIFLE,5.56 MILLIMETER          | 120         |
|  LANCASTER | RIFLE,5.56 MILLIMETER          | 120         |
|  LANCASTER | LIGHT ARMORED VEHICLE          | 0           |
|  LANCASTER | LIGHT ARMORED VEHICLE          | 0           |
|  LANCASTER | LIGHT ARMORED VEHICLE          | 0           |
|  LANCASTER | MINE RESISTANT VEHICLE         | 412000      |
|  LANCASTER | IMAGE INTENSIFIER,NIGHT VISION | 6800        |
|  LANCASTER | IMAGE INTENSIFIER,NIGHT VISION | 6800        |
|  LANCASTER | IMAGE INTENSIFIER,NIGHT VISION | 6800        |
|  LANCASTER | IMAGE INTENSIFIER,NIGHT VISION | 6800        |
|------------+--------------------------------+-------------|

csvgrep -c <-- 指定要過濾的欄位, -m <PATTERN> <-- 符合的模式(match PATTERN)
最後再透過 csvlook 美化顯示.

依照我們指定的欄位排序

如同UNIX指令中有sort,SQL中有order by.
csvkit提供了csvsort.

$ csvcut -c county,item_name,total_cost data.csv | csvgrep -c county -m LANCASTER | csvsort -c total_cost -r | csvlook
|------------+--------------------------------+-------------|
|  county    | item_name                      | total_cost  |
|------------+--------------------------------+-------------|
|  LANCASTER | MINE RESISTANT VEHICLE         | 412000      |
|  LANCASTER | IMAGE INTENSIFIER,NIGHT VISION | 6800        |
|  LANCASTER | IMAGE INTENSIFIER,NIGHT VISION | 6800        |
|  LANCASTER | IMAGE INTENSIFIER,NIGHT VISION | 6800        |
|  LANCASTER | IMAGE INTENSIFIER,NIGHT VISION | 6800        |
|  LANCASTER | RIFLE,5.56 MILLIMETER          | 120         |
|  LANCASTER | RIFLE,5.56 MILLIMETER          | 120         |
|  LANCASTER | RIFLE,5.56 MILLIMETER          | 120         |
|  LANCASTER | RIFLE,5.56 MILLIMETER          | 120         |
|  LANCASTER | RIFLE,5.56 MILLIMETER          | 120         |
|  LANCASTER | RIFLE,5.56 MILLIMETER          | 120         |
|  LANCASTER | RIFLE,5.56 MILLIMETER          | 120         |
|  LANCASTER | RIFLE,5.56 MILLIMETER          | 120         |
|  LANCASTER | RIFLE,5.56 MILLIMETER          | 120         |
|  LANCASTER | RIFLE,5.56 MILLIMETER          | 120         |
|  LANCASTER | LIGHT ARMORED VEHICLE          | 0           |
|  LANCASTER | LIGHT ARMORED VEHICLE          | 0           |
|  LANCASTER | LIGHT ARMORED VEHICLE          | 0           |
|------------+--------------------------------+-------------|

csvsort -c total_cost <-- 依據指定欄位排序 -r <-- 反向,default是升冪排序,反向即是降冪排序.

接著來看升冪排序.

$ csvcut -c county,item_name,total_cost data.csv | csvgrep -c county -m LANCASTER | csvsort -c total_cost | csvlook
|------------+--------------------------------+-------------|
|  county    | item_name                      | total_cost  |
|------------+--------------------------------+-------------|
|  LANCASTER | LIGHT ARMORED VEHICLE          | 0           |
|  LANCASTER | LIGHT ARMORED VEHICLE          | 0           |
|  LANCASTER | LIGHT ARMORED VEHICLE          | 0           |
|  LANCASTER | RIFLE,5.56 MILLIMETER          | 120         |
|  LANCASTER | RIFLE,5.56 MILLIMETER          | 120         |
|  LANCASTER | RIFLE,5.56 MILLIMETER          | 120         |
|  LANCASTER | RIFLE,5.56 MILLIMETER          | 120         |
|  LANCASTER | RIFLE,5.56 MILLIMETER          | 120         |
|  LANCASTER | RIFLE,5.56 MILLIMETER          | 120         |
|  LANCASTER | RIFLE,5.56 MILLIMETER          | 120         |
|  LANCASTER | RIFLE,5.56 MILLIMETER          | 120         |
|  LANCASTER | RIFLE,5.56 MILLIMETER          | 120         |
|  LANCASTER | RIFLE,5.56 MILLIMETER          | 120         |
|  LANCASTER | IMAGE INTENSIFIER,NIGHT VISION | 6800        |
|  LANCASTER | IMAGE INTENSIFIER,NIGHT VISION | 6800        |
|  LANCASTER | IMAGE INTENSIFIER,NIGHT VISION | 6800        |
|  LANCASTER | IMAGE INTENSIFIER,NIGHT VISION | 6800        |
|  LANCASTER | MINE RESISTANT VEHICLE         | 412000      |
|------------+--------------------------------+-------------|

不指定就是升冪排序.

上一篇
CSVKit 2
下一篇
CSVKit 4
系列文
蠻可愛的資料庫與資料處理30
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

尚未有邦友留言

立即登入留言